﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using SQLEditor.admin.entity;
using SQLEditor.dal;
using System.Web.Script.Serialization;
using System.Text.RegularExpressions;

namespace SQLEditor.admin.service
{
    public class SqlExerciseService : BaseService
    {
        private IAnswer answersql;

        public SqlExerciseService()
        {
            answersql = new MsSqlAnswer();
        }
        public string getProjectRemark(int projectId)
        {
            string remark = DB.sqlProject.FirstOrDefault(m => m.id == projectId).remark;
            return remark;
        }

        public string listQuestion(int userId)
        {
            //var list = from b in DB.sqlQuestion
            //           orderby b.id
            //           select new SqlQuestion
            //           {
            //               id = b.id,                           
            //               refAnswer = b.answer,
            //               explain = b.explain,
            //               point = b.point,
            //               projectId = b.sqlProject.id,
            //               //projectRemark = b.sqlProject.remark,
            //               status = b.sqlAnswer.Any(m =>(m.userId == userId && m.status == SqlAnswer.SUCCESS )) ? "Yes" : "No",
            //               title = b.title 

            //           };
            List<SqlQuestion> list = new List<SqlQuestion>();
            //id,title,answer,explain,point,projectId,status
            string sql = "p_getQuestionAnswerByUserId";
            SqlParameter pUserId = new SqlParameter("@userId", userId);
            DataTable dt = SQLHelper.GetTable(sql, new SqlParameter[] { pUserId }, CommandType.StoredProcedure);
            foreach(DataRow dr in dt.Rows)
            {
                SqlQuestion q = new SqlQuestion() {
                    id = ConvertUtil.toInt32(dr["id"]),
                    refAnswer = ConvertUtil.toString(dr["answer"]),
                    explain = ConvertUtil.toString(dr["explain"]),
                    point = ConvertUtil.toInt32(dr["point"]),
                    projectId = ConvertUtil.toInt32(dr["projectId"]),
                    status = ConvertUtil.toString(dr["status"]),
                    title = ConvertUtil.toString(dr["title"])
                };
                list.Add(q);
            }
            string json = JsonUtil.ToJson(list.ToList());
            return json;
        }

        public void answer()
        {


        }

        public SqlAnswer answer(int userId, int questionId, string answerSql)
        {
            
            //得到参考答案
            var question = DB.sqlQuestion.First(m => m.id == questionId);
            SqlAnswer qa = new SqlAnswer
            {
                questionId = question.id,
                point = question.point,
                userId = userId,                
                refAnswer = question.answer.Trim()
            };
            
            string refAnswer = qa.refAnswer.ToLower().Trim();
            if (refAnswer.StartsWith("create table"))
            {
                answersql.answerCreateTable(qa, questionId, answerSql);
                return qa;
            }
            else if (refAnswer.StartsWith("alter table"))
            {
                answersql.answerAlterTable(qa, questionId, answerSql);
                return qa;
            }
            else if (refAnswer.StartsWith("insert") || refAnswer.StartsWith("update") || refAnswer.StartsWith("delete"))
            {
                answersql.answerInsert(qa, questionId, answerSql);
                return qa;
            }else if(refAnswer.StartsWith("create view"))
            {
                answersql.answerCreateView(qa, questionId, answerSql);
                return qa;
            }
            else if (refAnswer.StartsWith("create index") || refAnswer.StartsWith("create unique index") || refAnswer.StartsWith("create clustered index") || refAnswer.StartsWith("create nonclustered index"))
            {
                answersql.answerCreateIndex(qa, questionId, answerSql);
                return qa;
            }
            int point = 0;
            if (answerSql == qa.refAnswer)
            {
                //与参考答案相同
                qa.status = SqlAnswer.SUCCESS;
                qa.statusStr = SqlAnswer.SUCCESS_STR1;
                
                try
                {
                    DataTable dt = answersql.select(answerSql);
                    string json = AnswerUtils.ToJson(dt);
                    qa.jsonAnswer = json;
                    qa.jsonRefAnswer = json;
                    point = qa.point;
                }
                catch (Exception ex)
                {
                    qa.status = SqlAnswer.FAIL_COMPILE;
                    qa.statusStr = "参考答案语法错误:" + ex.Message;
                }
                answersql.saveAnswerRecord(questionId, answerSql, qa.status, qa.statusStr, point, userId);
                return qa;
            }
            try
            {
                if (userId != 1)
                {
                    AnswerUtils.filterString(answerSql);
                }
            }
            catch (Exception ex)
            {
                qa.status = SqlAnswer.FAIL_COMPILE;
                qa.statusStr = "包含非法关键字:" + ex.Message;
                answersql.saveAnswerRecord(questionId, answerSql, qa.status, qa.statusStr, point, userId);
                return qa;
            }
            DataTable dtAnswer = null;
            DataTable dtRefAnswer = null;
            try
            {
                dtAnswer = answersql.select(answerSql);
                string json = AnswerUtils.ToJson(dtAnswer);
                qa.jsonAnswer = json;
            }
            catch (Exception ex)
            {
                qa.status = SqlAnswer.FAIL_COMPILE;
                qa.statusStr = "语法错误:" + ex.Message;
                qa.jsonAnswer = "[]";
                answersql.saveAnswerRecord(questionId, answerSql, qa.status, qa.statusStr, point, userId);
                return qa;
            }
            
            try
            {
                dtRefAnswer = answersql.select(qa.refAnswer);
                string json = AnswerUtils.ToJson(dtRefAnswer);
                qa.jsonRefAnswer = json;
            }
            catch (Exception ex)
            {
                qa.status = SqlAnswer.FAIL_COMPILE;
                qa.statusStr = "参考答案语法错误:" + ex.Message;
                qa.jsonRefAnswer = "[]";
                answersql.saveAnswerRecord(questionId, answerSql, qa.status, qa.statusStr, point, userId);
                return qa;
            }
            if (AnswerUtils.CompareDataTable(dtAnswer, dtRefAnswer))
            {
                qa.status = SqlAnswer.SUCCESS;
                qa.statusStr = SqlAnswer.SUCCESS_STR2;
                point = qa.point;
            }
            else
            {
                qa.status = SqlAnswer.FAIL_RESULT;
                qa.statusStr = SqlAnswer.FAIL_RESULT_STR;
            }
            answersql.saveAnswerRecord(questionId, answerSql, qa.status, qa.statusStr, point, userId);
            return qa;
        }
      
        public string test()
        {
            return answersql.test();
        }
    }
}